package com.eorchis.module.trainingplan.dao.impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.hibernate.Hibernate;
import org.springframework.stereotype.Repository;
import com.eorchis.constants.DeptConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.OrderType;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.classplanapply.domain.ClassPlanApply;
import com.eorchis.module.classplanapply.ui.commond.ClassPlanApplyQueryCommond;
import com.eorchis.module.classuser.domain.ClassUser;
import com.eorchis.module.trainingplan.dao.ITrainingPlanDao;
import com.eorchis.module.trainingplan.domain.ClassPlanApplyQueryBean;
import com.eorchis.module.trainingplan.domain.TrainingPlan;
import com.eorchis.module.trainingplan.domain.TrainingPlanFile;
import com.eorchis.module.trainingplan.domain.TrainingPlanFileZZB;
import com.eorchis.module.trainingplan.domain.yearlyPlanExport;
import com.eorchis.module.trainingplan.ui.commond.TrainingPlanFileValidCommond;
import com.eorchis.module.trainingplan.ui.commond.TrainingPlanFileZZBValidCommond;
import com.eorchis.module.trainingplan.ui.commond.TrainingPlanQueryCommond;
import com.eorchis.module.trainingplan.ui.commond.TrainingPlanValidCommond;
import com.eorchis.module.user.domain.User;
import com.eorchis.utils.BeanUtils;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: TrainingPlanDaoImpl<br>
 * Description: 培训计划数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * 
 * @author RongSL
 * @createDate 2016-07-12
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.trainingplan.dao.impl.TrainingPlanDaoImpl")
public class TrainingPlanDaoImpl extends HibernateAbstractBaseDao implements ITrainingPlanDao {

	public Class<? extends IBaseEntity> entityClass() {
		return TrainingPlan.class;
	}

	public void queryConditionProcessor(IConditionBuilder defaultQueryConditionBuilder, IQueryCommond queryCommond) {
		TrainingPlanQueryCommond trainingPlanQueryCommond = (TrainingPlanQueryCommond) queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM TrainingPlan t");
		defaultQueryConditionBuilder.addCondition("t.planId", CompareType.IN, trainingPlanQueryCommond.getSearchPlanIds());
		defaultQueryConditionBuilder.addCondition("t.applyNum", CompareType.EQUAL, trainingPlanQueryCommond.getSearchApplyNum());
		defaultQueryConditionBuilder.addCondition("t.auditState", CompareType.EQUAL, trainingPlanQueryCommond.getSearchAuditState());
		defaultQueryConditionBuilder.addCondition("t.className", CompareType.LIKE, trainingPlanQueryCommond.getSearchClassName());
		defaultQueryConditionBuilder.addCondition("t.issuedState", CompareType.EQUAL, trainingPlanQueryCommond.getSearchIssuedState());
		defaultQueryConditionBuilder.addCondition("t.publishState", CompareType.EQUAL, trainingPlanQueryCommond.getSearchPublishState());
		defaultQueryConditionBuilder.addCondition("t.activeState", CompareType.EQUAL, trainingPlanQueryCommond.getSearchActiveState());
		// defaultQueryConditionBuilder.addCondition("t.beginDate",
		// CompareType.GREATER_THAN_OR_EQUAL,trainingPlanQueryCommond.getSearchBeginDate());
		// defaultQueryConditionBuilder.addCondition("t.endDate",
		// CompareType.LESS_THAN_OR_EQUAL,trainingPlanQueryCommond.getSearchEndDate());
		defaultQueryConditionBuilder.addCondition("t.undertakeName", CompareType.EQUAL, trainingPlanQueryCommond.getSearchUndertakeName());
		defaultQueryConditionBuilder.addCondition("t.undertakeId", CompareType.EQUAL, trainingPlanQueryCommond.getSearchUndertakeID());

		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		defaultQueryConditionBuilder.addSort("t.createDate", OrderType.DESC);
	}

	/**
	 * 查看培训计划
	 * 
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public TrainingPlanValidCommond findTrainingPlan(TrainingPlanValidCommond vCommond) {
		TrainingPlan tp = super.find(TrainingPlan.class, vCommond.getPlanId());
		TrainingPlanValidCommond v=new TrainingPlanValidCommond();
		BeanUtils.copyProperties(tp, v);
		return v;
	}
	
	/**
	 * 获得培训计划最大编号
	 * @author ZhouYG
	 * @Date 2016-09-18
	 */
	public String getMaxApplyNum(String deptCode,Integer year){
		try{
			StringBuffer sb=new StringBuffer();
			sb.append(" select nvl(max(tp.applyNum),'");
			sb.append("P");
			sb.append(year);
			sb.append(deptCode);
			sb.append("00");
			sb.append("') from TrainingPlan tp ");
			sb.append(" where tp.applyNum like '%");
			sb.append(year);
			sb.append(deptCode);
			sb.append("%'");
			List<?> list=this.executeFind(QueryStringType.HQL, sb.toString(), null);
			return (String)list.get(0);
		}catch(Exception e){
			e.printStackTrace();
			return "P"+year+deptCode+"00";
		}
	}
	
	/**
	 * 获得培训班最大编号
	 * @author ZhouYG
	 * @Date 2016-09-18
	 */
	public String getMaxClassNum(String year){
		try{
			Map<String,Object> params=new HashMap<String,Object>();
			StringBuffer sb=new StringBuffer();
			sb.append(" select nvl(max(tc.classNum),'");
			sb.append(year);
			sb.append("000");
			sb.append("') from TrainingClass tc ");
			sb.append(" where tc.classNum like '");
			sb.append(year);
			sb.append("%' and tc.ministrieID not in (:breakDeptId) ");
			params.put("breakDeptId", DeptConstants.breakDepId);
			List<?> list=this.executeFind(QueryStringType.HQL, sb.toString(), params);
			return (String)list.get(0);
		}catch(Exception e){
			e.printStackTrace();
			return year+"000";
		}
	}
	
	/**
	 * 判断有无培训计划
	 * */
	public boolean TrainingPlanSearch(TrainingPlanValidCommond vCommond) {
		try{
			StringBuffer sql=new StringBuffer("select count(1) from Training_Plan  tp where 1=1 ");
			Map<String,Object> params=new HashMap<String,Object>();
			if(PropertyUtil.objectNotEmpty(vCommond.getApplyNum())){
				sql.append(" and tp.apply_Num=:an ");
				params.put("an", vCommond.getApplyNum().trim());
			}
			List<?> list=this.executeFind(QueryStringType.SQL, sql.toString(), params);
			if(PropertyUtil.objectNotEmpty(list)){
				if(Integer.valueOf((list.get(0)+""))>0){
					return true;
				}
			}
			return false;
		}catch(Exception e){
			e.printStackTrace();
			return true;
		}
	}
	
	/**
	 * 查看培训计划归档
	 * 
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public TrainingPlanFileValidCommond findTrainingPlan(TrainingPlanFileValidCommond vCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql ="";
		if(PropertyUtil.objectNotEmpty(vCommond.getVersion())){
			sql = " select t.plan_id from training_plan_file t where t.version=" + vCommond.getVersion() + " and t.apply_num='" + vCommond.getApplyNum()+"'";
		}else{
			sql=" select t.plan_id,max(t.version) from training_plan_file t where t.apply_num=:applyNum  group by t.plan_id,t.version order by t.version desc ";
			params.put("applyNum", vCommond.getApplyNum());
		}
		List<?> list = super.executeFind(QueryStringType.SQL, sql, params);
		if (PropertyUtil.objectNotEmpty(list)) {
			Object s[] = (Object[]) list.get(0);
			TrainingPlanFile tp = super.find(TrainingPlanFile.class, s[0].toString());
			BeanUtils.copyProperties(tp, vCommond);
		} else {
			vCommond = null;
		}
		return vCommond;
	}
	
	/**
	 * 查看培训计划中组部的最新归档
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public TrainingPlanFileZZBValidCommond findTrainingPlan(TrainingPlanFileZZBValidCommond vCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql ="";
		if(PropertyUtil.objectNotEmpty(vCommond.getVersion())){
			sql = " select t.plan_id from training_plan_file_ZZB t where t.version=" + vCommond.getVersion() + " and t.apply_num='" + vCommond.getApplyNum()+"'";
		}else{
			sql=" select t.plan_id,max(t.version) from training_plan_file_ZZB t where t.apply_num=:applyNum  group by t.plan_id,t.version order by t.version desc  ";
			params.put("applyNum", vCommond.getApplyNum());
		}
		List<?> list = super.executeFind(QueryStringType.SQL, sql, params);
		if (PropertyUtil.objectNotEmpty(list)) {
			Object s[] = (Object[]) list.get(0);
			TrainingPlanFileZZB tp = super.find(TrainingPlanFileZZB.class, s[0].toString());
			BeanUtils.copyProperties(tp, vCommond);
		} else {
			vCommond = null;
		}
		return vCommond;
	}

	// /**
	// * 查看培训计划最大编号
	// * @author RongSL
	// * @Date 2016-07-15
	// * */
	// @Override
	// public Integer findTrainingPlanNum(TrainingPlanFileValidCommond vCommond)
	// {
	// String
	// HQL="select t from TrainingPlanFile t where t.applyNum=:applyNum and t.version=(select max(tt.varsion) from TrainingPlanFile tt where tt.applyNum=:applyNum)";
	// Map<String,Object> params=new HashMap<String,Object>();
	// params.put("appNum", vCommond.getApplyNum());
	// List<TrainingPlanFileValidCommond>
	// list=(List<TrainingPlanFileValidCommond>)
	// super.executeFind(QueryStringType.HQL, HQL,
	// params,TrainingPlanFileValidCommond.class);
	// if(PropertyUtil.objectNotEmpty(list)){
	// vCommond=(TrainingPlanFileValidCommond) list.get(0);
	// }else
	// vCommond=new TrainingPlanFileValidCommond();
	// return vCommond;
	// }

	/**
	 * 判断计划是否可以撤回审核
	 * 
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public Boolean judgePlanWithdraw(TrainingPlanQueryCommond queryCommond) {
		String Sql = "select t.* from training_plan t where t.plan_id in (:ids) and t.issued_State <>:issuedState or t.audit_State<>:auditState";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("ids", queryCommond.getSearchPlanIds());
		params.put("issuedState", TrainingPlan.ISSUEDSTATE_W);
		params.put("auditState", TrainingPlan.AUDITSTATE_D);
		int i = super.executeFind(QueryStringType.SQL, Sql, params).size();
		if (i > 0)
			return false;
		else
			return true;
	}

	/**
	 * 判断计划是否可以删除
	 * 
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public Boolean judgePlanDelete(TrainingPlanQueryCommond queryCommond) {
		String Sql = "select t.* from training_plan t where t.plan_id in (:ids) and  t.audit_State<>:auditState";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("ids", queryCommond.getSearchPlanIds());
		params.put("auditState", TrainingPlan.AUDITSTATE_W);
		int i = super.executeFind(QueryStringType.SQL, Sql, params).size();
		if (i > 0)
			return false;
		else
			return true;
	}

	/**
	 * 判断计划是否可以驳回
	 * 
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public Boolean judgePlanReject(TrainingPlanQueryCommond queryCommond) {
		String Sql = "select t.* from training_plan t where t.plan_id in (:ids) and  t.issued_Date<>:issuedDate";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("ids", queryCommond.getSearchPlanIds());
		params.put("issuedDate", TrainingPlan.PUBLISHSTATE_W);
		int i = super.executeFind(QueryStringType.SQL, Sql, params).size();
		if (i > 0)
			return false;
		else
			return true;
	}

	/**
	 * 更新计划归档表
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public void updatePlanFile(TrainingPlanFileValidCommond ValidCommond, String array) {

		try {
			String HQL = " update TrainingPlanFile t set t.planApply=:apply where t.planId=:planId ";
			Map<String, Object> params = new HashMap<String, Object>();
			byte[] bytes = array.toString().getBytes("UTF-8");// String转byte
			Blob blobValue = Hibernate.createBlob(bytes);// byte转Blob
			params.put("apply", blobValue);
			params.put("planId", ValidCommond.getPlanId());
			this.executeUpdate(QueryStringType.HQL, HQL, params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新计划归档表
	 * @author RongSL
	 * @Date 2016-07-13
	 * */
	@Override
	public void updatePlanFile(TrainingPlanFileZZBValidCommond ValidCommond, String array) {
		
		try {
			String HQL = " update TrainingPlanFileZZB t set t.planApply=:apply where t.planId=:planId ";
			Map<String, Object> params = new HashMap<String, Object>();
			byte[] bytes = array.toString().getBytes("UTF-8");// String转byte
			Blob blobValue = Hibernate.createBlob(bytes);// byte转Blob
			params.put("apply", blobValue);
			params.put("planId", ValidCommond.getPlanId());
			this.executeUpdate(QueryStringType.HQL, HQL, params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 变更培训计划状态
	 * @author RongSL
	 * @Date 2016-07-28
	 * */
	@Override
	public void updatePlanState(TrainingPlanQueryCommond queryCommond) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String HQL = " update TrainingPlan t  ";
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
				HQL+=" set t.activeState=:state ";
				params.put("state", queryCommond.getSearchActiveState());
			}
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
				HQL+=" set t.publishState=:state ";
				params.put("state", queryCommond.getSearchPublishState());
			}
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState())){
				HQL+=" set t.auditState=:state ";
				params.put("state", queryCommond.getSearchAuditState());
			}
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchIssuedState())){
				HQL+=" set t.issuedState=:state ";
				params.put("state", queryCommond.getSearchIssuedState());
			}
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
				HQL+=" where t.planId in (:planIds) ";
				params.put("planIds", queryCommond.getSearchPlanIds());
			}
			this.executeUpdate(QueryStringType.HQL, HQL, params);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("变更培训计划状态方法缺少参数！");
		}
	}

	@SuppressWarnings("deprecation")
	/**
	 * 计划下发
	 * @author RongSL
	 * @Date 2016-07-21
	 * */
	public void planIssuedAdd(TrainingPlanQueryCommond queryCommond) {
			Connection con = super.getSession().connection();
			PreparedStatement ps = null;
			try {
				for (int i=0;i< queryCommond.getDeptIds().length;i++) {
					if(validateIssuedState(queryCommond.getSearchPlanId(),queryCommond.getDeptIds()[i])){
						String sql = "insert into plan_Issued values(?,sysdate,?,?,?,?,?)";
						ps = con.prepareStatement(sql);
						ps.setString(1, UUID.randomUUID().toString().replace("-", ""));
						ps.setInt(2, TrainingPlan.publicStatus_Y);
						ps.setString(3, queryCommond.getSearchPlanId());
						ps.setString(4, queryCommond.getDeptIds()[i]);
						ps.setString(5, queryCommond.getOpinions());
						ps.setInt(6,TrainingPlan.DF_ISSUEDSTATE_W);
					}else{
						String sql = "update plan_issued p set p.create_date=sysdate,p.active_state=?,p.opinion=?,p.feedback_state=?  where p.plan_id=? and p.dept_id=?";
						ps = con.prepareStatement(sql);
						ps.setInt(1, TrainingPlan.publicStatus_Y);
						ps.setString(2, queryCommond.getOpinions());
						ps.setInt(3, TrainingPlan.DF_ISSUEDSTATE_W);
						ps.setString(4, queryCommond.getSearchPlanId());
						ps.setString(5, queryCommond.getDeptIds()[i]);
					}
					ps.executeUpdate();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					ps.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
//		}
	}

	//验证次计划是否已下发
	public boolean validateIssuedState(String... id){
		Map<String,Object> params=new LinkedHashMap<String,Object>();
		String sql="select count(1) from PLAN_ISSUED t where t.plan_id=:planId and t.dept_id=:deptId ";
		params.put("planId", id[0]);
		params.put("deptId", id[1]);
		List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
		if(PropertyUtil.objectNotEmpty(list)){
			if(!list.get(0).toString().equals(0+"")){
				return false;
			}
		}
		return true;
	}
	
	/**
	 * 更新计划总人数
	 * 
	 * @author RongSL
	 * @Date 2016-07-25
	 * */
	public void updatePlanNum(TrainingPlanQueryCommond queryCommond) {
		String sql = " update training_plan set plan_num=:planNum where apply_num =:applyNum ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("applyNum", queryCommond.getSearchApplyNum());
		params.put("planNum", queryCommond.getSearchPlanNum());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 查看培训计划列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * */
	public List<TrainingPlanValidCommond> findPlanList(TrainingPlanQueryCommond queryCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer(" select plan_id \"planId\",plan_num \"planNum\",reject_reason \"rejectReason\"," +
				"apply_num \"applyNum\",plan_day \"planDay\",class_name \"className\",version \"version\", start_plan_date as \"startPlanDate\", end_plan_date as \"endPlanDate\"," +
				"audit_state \"auditState\",decode(audit_state,1,'审核通过',2,'建议修改',3,'待审核')   \"auditStateStr\"," +
				"create_date \"createDate\",issued_state \"issuedState\",issuedStr \"issuedStr\",publish_state \"publishState\"," +
				"decode(publish_state,1,'已发布',2,'未发布') \"publishStateStr\", versionState \"versionState\" " +
				"from ( select t.plan_id ,t.plan_num,  t.apply_num ,t.version,t.operation_date, "
				+ "t.plan_day ,   t.audit_state ,t.reject_reason, t.create_date ,t.class_name,t.undertake_id,t.start_plan_date,t.end_plan_date,"
				+ "t.issued_state , decode((select count(1) from training_plan_file_zzb tpp where tpp.apply_num = t.apply_num), 0, 2, 1) versionState, decode(t.issued_state, 1,  '已下发' || '(' || 0 || '/' || count(pi.issued_id)||')',"
				+ "2, '未下发',  3,  '已反馈' || '(' || (select count(distinct pii.dept_id)   from plan_Issued pii where pii.plan_id = t.plan_id  and pii.issued_id in "
				+ " (select poo.issued_id    from plan_Opinion poo  where poo.issued_id = pii.issued_id)) || '/' || "
				+ "count(pi.issued_id)||')') issuedStr, t.publish_state  from training_plan t  left join plan_Issued pi on t.plan_id = pi.plan_id  " +
				"group by t.plan_id,   t.plan_num,t.operation_date,  t.apply_num, t.reject_reason,t.undertake_id,t.plan_day, t.start_plan_date,t.end_plan_date," +
				"t.version, t.class_name, t.audit_state,t.issued_state,  t.publish_state,t.create_date ) b where 1=1 ");
		
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchApplyNum())) {
			sb.append(" and b.apply_Num =:applyNum ");
			params.put("applyNum", queryCommond.getSearchApplyNum());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			sb.append(" and b.class_Name like :className ");
			params.put("className", "%"+queryCommond.getSearchClassName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState())){
			sb.append(" and b.audit_State =:auditState ");
			params.put("auditState", queryCommond.getSearchAuditState());
		}else{
			sb.append(" and b.audit_State <>:auditState   ");
			params.put("auditState", TrainingPlan.AUDITSTATE_W);
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			sb.append(" and b.publish_State =:publishState ");
			params.put("publishState", queryCommond.getSearchPublishState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchIssuedState())){
			sb.append(" and b.Issued_State =:IssuedState ");
			params.put("IssuedState", queryCommond.getSearchIssuedState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUndertakeID())){
			sb.append(" and b.undertake_Id =:undertakeId ");
			params.put("undertakeId",queryCommond.getSearchUndertakeID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
			sb.append(" and b.plan_id in(:planIds) ");
			params.put("planIds",queryCommond.getSearchPlanIds());
		}
		//查询总数量
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			try {
				sb.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			sb.append(" order by apply_num ");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingPlanValidCommond.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		//查询班级数量
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		Long count = this.count(queryCondition);
		List<TrainingPlanValidCommond> resultList=new ArrayList<TrainingPlanValidCommond>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition);
		}else {
			int firstResult = queryCommond.calculate(count.longValue());
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 查看培训计划列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * */
	public List<TrainingPlanValidCommond> findPlanListBW(TrainingPlanQueryCommond queryCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer(" select plan_id \"planId\",plan_num \"planNum\",reject_reason \"rejectReason\"," +
				"apply_num \"applyNum\",plan_day \"planDay\",class_name \"className\",version \"version\",start_plan_date as \"startPlanDate\",end_plan_date as \"endPlanDate\"," +
				"audit_state \"auditState\",decode(audit_state,1,'审核通过',2,'建议修改',3,'待审核')   \"auditStateStr\"," +
				"create_date \"createDate\",issued_state \"issuedState\",issuedStr \"issuedStr\",publish_state \"publishState\"," +
				" decode(publish_state,1,'已发布',2,'未发布') \"publishStateStr\",versionState \"versionState\" " +
				"from ( select t.plan_id ,t.plan_num, t.reject_reason, t.apply_num ,t.version,t.operation_date, "
				+ "t.plan_day ,   t.audit_state , t.create_date ,t.class_name,t.undertake_id,t.start_plan_date,t.end_plan_date,"
				+ "t.issued_state , decode((select count(1) from training_plan_file tpp where tpp.apply_num = t.apply_num), 0, 2, 1) versionState, decode(t.issued_state, 1,  '已下发' || '(' || 0 || '/' || count(pi.issued_id)||')',"
				+ "2, '未下发',  3,  '已反馈' || '(' || (select count(distinct pii.dept_id)   from plan_Issued pii where pii.plan_id = t.plan_id  and pii.issued_id in "
				+ " (select poo.issued_id    from plan_Opinion poo  where poo.issued_id = pii.issued_id)) || '/' || "
				+ "count(pi.issued_id)||')') issuedStr, t.publish_state  from training_plan t  left join plan_Issued pi on t.plan_id = pi.plan_id  ");
		sb.append(" group by t.plan_id,   t.plan_num,t.operation_date, t.reject_reason, t.apply_num,t.start_plan_date," +
				"t.end_plan_date, t.undertake_id,t.plan_day,t.version, " +
				"t.class_name, t.audit_state,t.issued_state,  t.publish_state,t.create_date ) b where 1=1 ");
		
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchApplyNum())) {
			sb.append(" and b.apply_Num =:applyNum ");
			params.put("applyNum", queryCommond.getSearchApplyNum());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			sb.append(" and b.class_Name like :className ");
			params.put("className", "%"+queryCommond.getSearchClassName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState())){
			sb.append(" and b.audit_State =:auditState ");
			params.put("auditState", queryCommond.getSearchAuditState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			sb.append(" and b.publish_State =:publishState ");
			params.put("publishState", queryCommond.getSearchPublishState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchIssuedState())){
			sb.append(" and b.Issued_State =:IssuedState ");
			params.put("IssuedState", queryCommond.getSearchIssuedState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUndertakeID())){
			sb.append(" and b.undertake_Id =:undertakeId ");
			params.put("undertakeId",queryCommond.getSearchUndertakeID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
			sb.append(" and b.plan_id in(:planIds) ");
			params.put("planIds",queryCommond.getSearchPlanIds());
		}
		//查询总数量
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			try {
				sb.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			sb.append(" order by apply_num ");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingPlanValidCommond.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		//查询班级数量
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		Long count = this.count(queryCondition);
		List<TrainingPlanValidCommond> resultList=new ArrayList<TrainingPlanValidCommond>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition);
		}else {
			int firstResult = queryCommond.calculate(count.longValue());
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 地方查看培训计划列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * */
	public List<TrainingPlanValidCommond> findPlanListDF(TrainingPlanQueryCommond queryCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer(" select plan_id      \"planId\",   plan_num      \"planNum\", reject_reason \"rejectReason\", apply_num    \"applyNum\",   " +
				"plan_day      \"planDay\",   class_name    \"className\",    version      \"version\", start_plan_date as \"startPlanDate\", end_plan_date as \"endPlanDate\", " +
				"audit_state   \"auditState\", decode(audit_state,1,'审核通过',2,'建议修改',3,'待审核')   \"auditStateStr\",create_date   \"createDate\",  issued_state  \"issuedState\"," +
				" feedbackState \"feedbackState\", publish_state \"publishState\",decode(publish_state,1,'已发布',2,'未发布') \"publishStateStr\", versionState  \"versionState\" " +
				"from (select t.plan_id,bd.depname,bd.depid, t.plan_num, t.apply_num, t.version, t.operation_date,t.start_plan_date, t.end_plan_date, " +
				"t.plan_day,  t.audit_state, t.reject_reason, t.create_date,  t.class_name, t.undertake_id, t.issued_state, " +
				"decode((select count(1) from training_plan_file tpp where tpp.apply_num = t.apply_num), 0, 2, 1) versionState, " +
				"pi.feedback_state feedbackState,  t.publish_state from plan_Issued pi  " +
				"left join training_plan t on t.plan_id = pi.plan_id  left join base_department bd on pi.dept_id=bd.depid " +
				"group by t.plan_id,pi.feedback_state,bd.depname,bd.depid, t.plan_num, t.operation_date, t.apply_num, t.undertake_id,t.start_plan_date,  t.end_plan_date, " +
				" t.plan_day, t.version, t.class_name, t.reject_reason,t.audit_state, t.issued_state, t.publish_state, t.create_date ) b where 1 = 1 ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchApplyNum())) {
			sb.append(" and b.apply_Num =:applyNum ");
			params.put("applyNum",queryCommond.getSearchApplyNum());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			sb.append(" and b.class_Name like :className ");
			params.put("className", "%"+queryCommond.getSearchClassName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchFeedbackState())){
			sb.append(" and b.feedbackState =:seedbackState ");
			params.put("seedbackState", queryCommond.getSearchFeedbackState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptId())){
			sb.append(" and b.depid=:deptId ");
			params.put("deptId", queryCommond.getSearchDeptId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
			sb.append(" and b.plan_id in(:planIds) ");
			params.put("planIds",queryCommond.getSearchPlanIds());
		}
		//查询总数量
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			try {
				sb.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			sb.append(" order by operation_date desc,create_date desc  ");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingPlanValidCommond.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		//查询班级数量
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		queryCondition.setQueryStringType(QueryStringType.SQL);
		Long count = this.count(queryCondition);
		List<TrainingPlanValidCommond> resultList=new ArrayList<TrainingPlanValidCommond>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition);
		}else {
			int firstResult = queryCommond.calculate(count.longValue());
			resultList=(List<TrainingPlanValidCommond>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 查看已下发列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * */
	public List<TrainingPlanValidCommond> lookIssuedList(TrainingPlanQueryCommond queryCommond){
		StringBuffer sb=new StringBuffer(" select bd.depname \"deptName\",bd.depid \"deptId\",to_char(pi.create_date,'yyyy-mm-dd') \"createDateStr\" from plan_Issued pi left join base_department bd on pi.dept_id=bd.depid where pi.plan_id=:planId order by bd.order_num");
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("planId", queryCommond.getSearchPlanId());
		return (List<TrainingPlanValidCommond>) this.executeFind(QueryStringType.SQL, sb.toString(), params,TrainingPlanValidCommond.class);
	}
	
	/**
	 * 下发单位列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * */
	public List<TrainingPlanValidCommond> findIssuedList(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sql=new StringBuffer("select deptName \"deptName\",selectState \"selectState\",deptId \"deptId\",to_char(orderNum) \"orderNum\" " +
				"from ((select bd.depname deptName, (select 1 from dual) selectState,bd.order_num orderNum, tcpa.dept_id deptId " +
				"from training_class_plan_apply tcpa  left join plan_Issued pi on tcpa.dept_id = pi.dept_id " +
				"left join base_department bd on pi.dept_id = bd.depid where pi.plan_id =:planId and bd.depid not in (:blackDepId)) union " +
				"(select bd.depname, (select 2 from dual) selectState,bd.order_num orderNum, tcpa.dept_id deptId from training_class_plan_apply tcpa " +
				"left join base_department bd on tcpa.dept_id = bd.depid where tcpa.dept_id not in (select bd.depid " +
				" from plan_Issued pi  left join base_department bd   on pi.dept_id = bd.depid  where pi.plan_id =:planId ) and bd.depid not in (:blackDepId) )) where 1=1 " );
				if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSelectState())){
					sql.append(" and selectState=:selectState ");
					params.put("selectState", queryCommond.getSearchSelectState());
				}
				sql.append(" order by orderNum");
		params.put("planId", queryCommond.getSearchPlanId());
		params.put("blackDepId", DeptConstants.blackDepId);
		return (List<TrainingPlanValidCommond>) this.executeFind(QueryStringType.SQL, sql.toString(), params,TrainingPlanValidCommond.class);
	}
	
	/**
	 * 查看反馈意见列表
	 * @Date 2016-07-25
	 * @author RongSL
	 * @return 
	 * */
	public List<TrainingPlanValidCommond> findFeedbackList(TrainingPlanQueryCommond queryCommond){
		String sql="select bd.depname \"deptName\",bd.depid \"deptId\",pi.plan_id \"planId\",pi.issued_id \"issuedId\", to_char(pi.create_date, 'yyyy-mm-dd hh24:mi:ss') \"issuedDateStr\", " +
				"to_char(poo.create_date, 'yyyy-mm-dd hh24:mi:ss') \"feedbackStr\", decode(poo.create_date, null, 3, (decode(poo.opinion_state,1,1,2))) \"selectState\" " +
				"from plan_Issued pi left join base_department bd  on pi.dept_id = bd.depid " +
				"left join (select po.create_date, po.issued_id,po.opinion_state from plan_Opinion po) poo on poo.issued_id = pi.issued_id " +
				"where pi.plan_id =:planId and bd.depid not in (:blackDepId) group by bd.depname,bd.depid, bd.order_num,pi.create_date,poo.create_date,poo.opinion_state,pi.plan_id,pi.issued_id " +
				"order by bd.order_num,poo.create_date desc nulls last ";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("planId", queryCommond.getSearchPlanId());
		params.put("blackDepId", DeptConstants.blackDepId);
		return (List<TrainingPlanValidCommond>) this.executeFind(QueryStringType.SQL, sql, params,TrainingPlanValidCommond.class);
	}
	
	@SuppressWarnings("deprecation")
	@Override   
	public List<ClassPlanApplyQueryBean> findClassPlanApplyQueryBeanList(ClassPlanApplyQueryCommond queryCommond) {
		Map<String,Object> params = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer("select cp.PLAN_APPLY_ID as \"planApplyId\",cp.ASIGN_CITY_COUNT as \"asignCityCount\"," +
				"cp.ASIGN_COUNT as \"asignCount\",cp.ASIGN_COUNTY_COUNT as \"asignCountyCount\"," +
				"cp.CITY_PLAN_COUNT as \"cityPlanCount\",cp.CLASS_ID as \"classId\", cp.COUNTY_PLAN_COUNT as \"countyPlanCount\"," +
				"cp.DEPT_ID as \"deptId\",cp.DEPT_NAME as \"deptCode\",cp.PLAN_COUNT as \"planCount\"," +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state =:activeState " +
				"and cu.class_id = cp.CLASS_ID and du.depid = cp.DEPT_ID and u.administrative_level =:cityCode and cu.auditing_state <> :auditingN) as \"reportedCityCount\"," +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state =:activeState " +
				"and cu.class_id = cp.CLASS_ID and du.depid = cp.DEPT_ID and u.administrative_level =:countyCode " +
				"and cu.auditing_state <> :auditingN) as \"reportedCountyCount\" " +
				"from TRAINING_CLASS_PLAN_APPLY cp left join base_department bd on cp.dept_id=bd.depid where 1=1 ");
		params.put("cityCode", ClassPlanApply.LEVEL_CITY);
		params.put("countyCode", ClassPlanApply.LEVEL_COUNTY);
		params.put("activeState", User.IS_ACTIVE_Y);
		params.put("auditingN", ClassUser.IS_AUDITING_N);
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			sql.append(" and cp.CLASS_ID =:classId");
			params.put("classId", queryCommond.getSearchClassId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptId())){
			sql.append(" and cp.DEPT_ID =:deptId");
			params.put("deptId", queryCommond.getSearchDeptId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptCode())){
			sql.append(" and cp.DEPT_CODE <> :deptCode");
			params.put("deptCode", queryCommond.getSearchDeptCode());
		}
		sql.append(" order by bd.order_num");
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassPlanApplyQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		List<ClassPlanApplyQueryBean> resultList=(List<ClassPlanApplyQueryBean>) this.findList(queryCondition);
		return resultList;
	}
	
	/**
	 * 查询所有培训班计划名额
	 * @author ZhouYG
	 * @createDate 2016-09-20
	 */
	@Override   
	public List<ClassPlanApplyQueryBean> findClassPlanApplyList(TrainingPlanQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" SELECT tc.dept_id           as \"deptId\", tc.dept_code         as \"deptCode\", " +
				"tp.class_name        as \"className\",  tc.city_plan_count   as \"cityPlanCount\",  tc.county_plan_count as \"countyPlanCount\", " +
				"tc.plan_count        as \"planCount\"  FROM training_class_plan_apply tc LEFT JOIN training_plan tp ON tc.class_id = tp.apply_num " +
				"WHERE tc.dept_id = :deptId AND tc.class_id IN (SELECT apply_num as \"applyNum\"  FROM (SELECT pi.dept_id, t.apply_num " +
				" FROM plan_Issued pi LEFT JOIN training_plan t ON t.plan_id = pi.plan_id LEFT JOIN base_department bd ON pi.dept_id = bd.depid) b  " +
				"WHERE 1 = 1  AND b.dept_id = tc.dept_id) ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchApplyNums())){
			sql.append(" and tc.class_id in (:applyNums) ");
			params.put("applyNums", queryCommond.getSearchApplyNums());
		}
		params.put("deptId", queryCommond.getSearchDeptId());
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassPlanApplyQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		List<ClassPlanApplyQueryBean> resultList=(List<ClassPlanApplyQueryBean>) this.findList(queryCondition);
		return resultList;
	}
	
	/**
	 * 删除计划相关计划名额
	 * @Date 2016-07-31
	 * */
	public void deletePlanApply(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" delete from TRAINING_CLASS_PLAN_APPLY t where t.class_id in (:applyNums) ";
		params.put("applyNums", queryCommond.getApplyNums());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 删除下发表数据
	 * @Date 2016-07-31
	 * */
	public void deletePlanIssued(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" delete from plan_Issued pi where pi.plan_id in (:planIds) ";
		params.put("planIds", queryCommond.getSearchPlanIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 删除计划意见数据
	 * @Date 2016-07-31
	 * */
	public void deletePlanOpinion(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" delete from plan_Opinion po where po.issued_id in (select pi.issued_id from plan_Issued pi where pi.plan_id in (:planIds)) ";
		params.put("planIds", queryCommond.getSearchPlanIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 删除计划归档数据
	 * @Date 2016-07-31
	 * */
	public void deletePlanFile(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" delete from TRAINING_PLAN_FILE t where t.apply_num in (:applyNums) ";
		params.put("applyNums", queryCommond.getApplyNums());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 根据部门名称查找部门ID
	 * @Date 2016-08-08
	 * */
	public String findAdminDeptId(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" select bd.depid from base_department bd where bd.parent_id=:bwId and bd.depname=:deptName ";
		params.put("deptName", queryCommond.getDeptName());
		params.put("bwId", DeptConstants.BW_DEPT_ROOT_ID);
		List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
		if(PropertyUtil.objectNotEmpty(list)){
			Object o=list.get(0);
			return o.toString();
		}
		return null;
	}
	
	/**
	 * 删除计划归档数据
	 * @Date 2016-07-31
	 * */
	public void deletePlanFileZZB(TrainingPlanQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" delete from TRAINING_PLAN_FILE_ZZB t where t.apply_num in (:applyNums) ";
		params.put("applyNums", queryCommond.getApplyNums());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 更新培训计划名额
	 * @Date 2016-08-01
	 * */
	public void updatePlanApply(String... id){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql=" update TRAINING_CLASS_PLAN_APPLY tc set tc.class_id=:classId where tc.class_id=:applyNum ";
		params.put("classId", id[1]);
		params.put("applyNum", id[0]);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	public void savaTest(TrainingPlan tp){
		this.save(tp);
	}
	
	/**
	 * 根据计划id查找地方部门id
	 * @author RongSL
	 * @date 2016-08-15
	 * */
	public String[] findDeptId(TrainingPlanValidCommond commond){
		String sql="select pi.dept_id from plan_issued pi where pi.plan_id=:planId";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("planId", commond.getPlanId());
		List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
		String []ids = null;
		if(PropertyUtil.objectNotEmpty(list)){
			ids=new String[list.size()];
			for(int i=0;i<list.size();i++){
				ids[i]= list.get(i).toString();
			}
		}
		return ids;
	}
	
	/**
	 * 获得中组部下发时附加消息
	 * @author RongSL
	 * @Date 2016-09-05
	 * */
	@Override
	public String getOpinion(TrainingPlanValidCommond commond){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql="select p.opinion from plan_issued p where p.plan_id=:planId and p.dept_id=:depId ";
		params.put("planId", commond.getPlanId());
		params.put("depId", commond.getDeptId());
		List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
		if(PropertyUtil.objectNotEmpty(list)){
			String opinion=list.get(0)+"";
			return opinion;
		}
		return "无";
	}
	
	/**
	 * 年度培训计划名额分配表
	 * @author RongSL
	 * @Date 2016-09-24
	 * */
	@Override
	public List<yearlyPlanExport> ExportYearPlan(TrainingPlanQueryCommond queryCommond) throws Exception{
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select to_char(t1.cityPlanCount) \"cityPlanCount\", " +
				"to_char(t2.countyPlanCount) \"countyPlanCount\", to_char(t3.planCount) \"planCount\", " +
				"t1.cityPlanCountSum \"cityPlanCountSum\", t2.countyPlanCountSum \"countyPlanCountSum\",  " +
				"t3.planCountSum \"planCountSum\", to_char(t4.deptName) \"deptName\",  tp.class_name \"className\", " +
				"tp.apply_num \"applyNum\", tp.undertake_name \"undertakeName\", tp.undertake_id \"undertakeId\" " +
				"from training_plan tp  inner join (select cityPlanCount, applyNum, cityPlanCountSum " +
				"from (select wm_concat(tcpa.city_plan_count) over(partition BY tp.apply_num order by bd.order_num) cityPlanCount, " +
				"sum(tcpa.city_plan_count) over(partition BY tp.apply_num order by bd.order_num) cityPlanCountSum, " +
				"tp.apply_num applyNum, row_number() over(partition BY tp.apply_num order by bd.order_num) row1 " +
				"from training_plan tp, training_class_plan_apply tcpa,base_department bd where tcpa.class_id = tp.apply_num " +
				"and tcpa.dept_id=bd.depid and tcpa.dept_id not in (:blackDepId) group by tp.apply_num, bd.order_num," +
				"tcpa.city_plan_count, tcpa.dept_id) t1 where row1 =:deptCount) t1 on t1.applyNum = tp.apply_num " +
				"inner join (select countyPlanCount, applyNum, countyPlanCountSum " +
				"from (select wm_concat(tcpa.county_plan_count) over(partition BY tp.apply_num order by bd.order_num) countyPlanCount, " +
				"sum(tcpa.county_plan_count) over(partition BY tp.apply_num order by bd.order_num) countyPlanCountSum, " +
				"tp.apply_num applyNum, row_number() over(partition BY tp.apply_num order by bd.order_num) row1 " +
				"from training_plan tp, training_class_plan_apply tcpa,base_department bd where tcpa.class_id = tp.apply_num and tcpa.dept_id=bd.depid and tcpa.dept_id not in (:blackDepId) " +
				"group by tp.apply_num,bd.order_num,  tcpa.county_plan_count,  tcpa.dept_id) t3 where row1 =:deptCount) t2 on t2.applyNum = tp.apply_num " +
				"inner join (select planCount, applyNum, planCountSum " +
				"from (select wm_concat(tcpa.plan_count) over(partition BY tp.apply_num order by bd.order_num) planCount, " +
				"sum(tcpa.plan_count) over(partition BY tp.apply_num order by bd.order_num) planCountSum, tp.apply_num applyNum, " +
				"row_number() over(partition BY tp.apply_num order by bd.order_num) row1 " +
				"from training_plan tp, training_class_plan_apply tcpa,base_department bd where tcpa.class_id = tp.apply_num and tcpa.dept_id=bd.depid  and tcpa.dept_id not in (:blackDepId) " +
				"group by tp.apply_num,bd.order_num, tcpa.plan_count, tcpa.dept_id) t4 where row1 =:deptCount and " +
				"planCountSum <> 0) t3 on t3.applyNum = tp.apply_num inner join (select deptName, applyNum " +
				"from (select wm_concat(tcpa.dept_name) over(partition BY tp.apply_num order by bd.order_num) deptName, " +
				"tp.apply_num applyNum, row_number() over(partition BY tp.apply_num order by bd.order_num) row1 " +
				"from training_plan tp, training_class_plan_apply tcpa,base_department bd where tcpa.class_id = tp.apply_num and tcpa.dept_id=bd.depid and tcpa.dept_id not in (:blackDepId) " +
				"group by tp.apply_num, bd.order_num,tcpa.dept_name, tcpa.dept_id) t1 where row1 =:deptCount) t4 on t4.applyNum = tp.apply_num  where 1=1 and tp.audit_state<>:noTrainingPlan");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
			sb.append(" and tp.plan_id in (:planIds) ");
			params.put("planIds", queryCommond.getSearchPlanIds());
		}
		params.put("deptCount", yearlyPlanExport.deptCount);
		params.put("noTrainingPlan", TrainingPlan.AUDITSTATE_F);
		params.put("blackDepId", DeptConstants.blackDepId);
		List<yearlyPlanExport> list=(List<yearlyPlanExport>) this.executeFind(QueryStringType.SQL, sb.toString(), params,yearlyPlanExport.class);
		return list;
	}
}
